package com.tbsoft.ehr.dao.attendance;

import java.sql.SQLException;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.transform.Transformers;
import org.springframework.orm.hibernate3.HibernateCallback;

import com.tbsoft.ehr.dao.BaseDaoImpl;
import com.tbsoft.ehr.model.AttendanceReport;
import com.tbsoft.ehr.model.BaseEntity;

/**
 * @desc 报表dao
 * @author vastdream
 * 
 */
public class ReportDaoImpl extends BaseDaoImpl<BaseEntity> implements ReportDao {

	@SuppressWarnings("unchecked")
	@Override
	public List<AttendanceReport> getsearchUserList(final String dateStart,final String dateEnd,final String name,final String scId) {
		return (List<AttendanceReport>) getHibernateTemplate().execute(new HibernateCallback() {

			@Override
			public Object doInHibernate(Session session) throws HibernateException, SQLException {
				String sql = "SELECT si.USER_ID AS userId,si.STAFF_SEX as sex, si.STAFF_NAME AS userName,IFNULL(sc.SC_NAME,sc2.SC_NAME) AS serviceName, IFNULL(sc.WORKING_HOUR,sc2.WORKING_HOUR) AS normalHours, ad.AD_DATE AS date, ad.AD_HOURS As leaveHours"
						+ ", ad.AD_TYPE as adType FROM EHR_STAFFINFO si"
						+ " LEFT JOIN EHR_ATTENDANCE ad ON ad.STAFF_ID = si.STAFF_ID AND  ad.STATUS =  'A'";
				if(dateStart != null && !dateStart.isEmpty()){
					sql += " and (ad.AD_DATE between '"+ dateStart+"' and '"+dateEnd+"' )";
				}
						sql+= " LEFT JOIN EHR_SERVICECUSTOMER sc ON  sc.sc_id = ad.sc_id AND sc.STATUS =  'A'"
						+ " LEFT JOIN EHR_SERVICECUSTOMER sc2 ON  sc2.sc_id = si.sc_id AND sc2.STATUS =  'A'"
						+ " WHERE si.STATUS =  'A'";
				if(scId != null && !scId.isEmpty()){
					sql +=" and IFNULL(sc.SC_ID,sc2.SC_ID) = '"+scId+"'";
				}
				if(name != null && !name.isEmpty()){
						sql += " and (si.USER_ID like '"+name+"%' or si.STAFF_NAME like '"+name+"%')";
				}
				sql += " order by si.USER_ID,ad.AD_DATE";
				SQLQuery query = session.createSQLQuery(sql);
				//query.addEntity(AttendanceReport.class);
				query.addScalar("userId", new org.hibernate.type.StringType());
				query.addScalar("sex", new org.hibernate.type.StringType());
				query.addScalar("userName", new org.hibernate.type.StringType());
				query.addScalar("serviceName", new org.hibernate.type.StringType());
				query.addScalar("normalHours", new org.hibernate.type.BigDecimalType());
				query.addScalar("date", new org.hibernate.type.DateType());
				query.addScalar("leaveHours", new org.hibernate.type.BigDecimalType());
				query.addScalar("adType", new org.hibernate.type.StringType());
				query.setResultTransformer(Transformers.aliasToBean(AttendanceReport.class));
				List<?> list= query.list();
				return list;
			}
		});
	}

}
